package org.cunamas.core.dao.impl;

import java.util.List;

import javax.faces.model.SelectItem;
import javax.sql.DataSource;

import org.cunamas.core.dao.UbigeoDao;
import org.cunamas.core.dto.mapper.SelectItemMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;


@Component
public class UbigeoDaoJdcImpl implements UbigeoDao {
	
	private JdbcTemplate jdbcTemplate;
	
	private final static String QUERY_DEP="select distinct SUBSTRING(t.CODUBIGEO,1,2) as cod_dep  ,t.DEP as nom_dep " +
										  "from TUbigeo t where t.EST = 1 order by t.DEP";
	
	private final static String QUERY_PROV="select distinct SUBSTRING(t.CODUBIGEO,3,2) as cod_pro ,t.PRO as nom_pro " +
											"from TUbigeo t where t.EST = 1 and SUBSTRING(t.CODUBIGEO,1,2) = ? order by t.PRO";
	
	private final static String QUERY_DIST="select distinct SUBSTRING(t.CODUBIGEO,5,2) as cod_dis , t.DIS as nom_dis " +
											"from TUbigeo t where t.EST = 1 and SUBSTRING(t.CODUBIGEO,1,4) = ? order by t.DIS";

	
	private final static String QUERY_SEDE="select distinct t.GRUPOSEDE as cod_gsede  ,t.NOM as nom_gsede " +
											"from TSedeGrupo t where t.CODPRG = '001' and t.EST = 1 and t.CODUBIGEO like ?";
	
	
	private final static String QUERY_COMITE ="SELECT distinct T.CODCOMGES as cod_com  ,T.NOM as nom_com " +
											  "FROM TComiteGestion T WHERE T.CODPRG = '001' AND T.CODUBIGEO = ?";
	
		
	private final static String QUERY_MAP_DEP="SELECT distinct t.DEP as cod_dep,t.DEP as nom_dep " +
			  									"FROM TUbigeo t where t.EST = 1";
	
	private final static String QUERY_MAP_PRO="SELECT distinct t.DEP + '_' + t.PRO as cod_pro,t.PRO as nom_pro " +
												"FROM TUbigeo t where t.EST = 1";
	
	@Autowired
	public void setJdbcTemplate(DataSource dataSource) {
		 this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	/* (non-Javadoc)
	 * @see org.cunamas.core.dao.impl.UbigeoDao#retornaDepartamentos()
	 */
	public List<SelectItem> retornaDepartamentos(){
		
		 return jdbcTemplate.query(QUERY_DEP,new SelectItemMapper());
	}
	
	
	/* (non-Javadoc)
	 * @see org.cunamas.core.dao.impl.UbigeoDao#retornaProvincia(java.lang.String)
	 */
	public List<SelectItem> retornaProvincia(String dep){
		
		 return jdbcTemplate.query(QUERY_PROV,new Object[] {dep},new SelectItemMapper());
	}
	
	 
	/* (non-Javadoc)
	 * @see org.cunamas.core.dao.impl.UbigeoDao#retornaDistrito(java.lang.String)
	 */
	public List<SelectItem> retornaDistrito(String pro){
	
		 return jdbcTemplate.query(QUERY_DIST,new Object[] {pro},new SelectItemMapper());
	}
	
	
	public List<SelectItem> retornaSede(String dep){
		
		 return jdbcTemplate.query(QUERY_SEDE,new Object[] {dep+"%"},new SelectItemMapper());
	}
	
	public List<SelectItem> retornaComite(String ubigeo){
		
		 return jdbcTemplate.query(QUERY_COMITE,new Object[] {ubigeo},new SelectItemMapper());
	}

	public List<SelectItem> retornaMapDepartamentos(){
		 return jdbcTemplate.query(QUERY_MAP_DEP,new SelectItemMapper());
	}
	
	public List<SelectItem> retornaMapProvincias(){
		 return jdbcTemplate.query(QUERY_MAP_PRO,new SelectItemMapper());
	}
}
